import os
import mysql.connector
import redis
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

# MySQL Database configuration
DB_CONFIG = {
    'host': os.getenv('DB_HOST', 'localhost'),
    'user': os.getenv('DB_USER', 'root'),
    'password': os.getenv('DB_PASSWORD', '200303'),
    'database': os.getenv('DB_NAME', 'attendance_system'),
    'port': int(os.getenv('DB_PORT', 3306)),
    'charset': 'utf8mb4',
    'collation': 'utf8mb4_unicode_ci'
}

# Redis configuration
REDIS_CONFIG = {
    'host': os.getenv('REDIS_HOST', 'localhost'),
    'port': int(os.getenv('REDIS_PORT', 6379)),
    'db': int(os.getenv('REDIS_DB', 0)),
    'password': os.getenv('REDIS_PASSWORD', None)
}

def get_mysql_connection():
    """
    Get a connection to the MySQL database.
    
    Returns:
        mysql.connector.connection.MySQLConnection: A connection to the MySQL database.
    """
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        return conn
    except mysql.connector.Error as err:
        print(f"Error connecting to MySQL database: {err}")
        raise

def get_redis_connection():
    """
    Get a connection to the Redis database.
    
    Returns:
        redis.Redis: A connection to the Redis database.
    """
    try:
        # Remove None password to avoid authentication errors
        redis_config = REDIS_CONFIG.copy()
        if redis_config['password'] is None:
            del redis_config['password']
            
        conn = redis.Redis(**redis_config)
        return conn
    except redis.RedisError as err:
        print(f"Error connecting to Redis: {err}")
        raise

def close_mysql_connection(conn):
    """
    Close a MySQL database connection.
    
    Args:
        conn (mysql.connector.connection.MySQLConnection): The connection to close.
    """
    if conn:
        conn.close()

def execute_query(query, params=None, fetch=True):
    """
    Execute a SQL query and return the results.
    
    Args:
        query (str): The SQL query to execute.
        params (tuple, optional): Parameters for the query. Defaults to None.
        fetch (bool, optional): Whether to fetch results. Defaults to True.
        
    Returns:
        list: The query results if fetch is True, otherwise None.
    """
    conn = None
    cursor = None
    try:
        conn = get_mysql_connection()
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute(query, params or ())
        
        if fetch:
            return cursor.fetchall()   # 获取所有结果
        else:
            conn.commit()    # 执行插入、更新、删除等操作
            return cursor.lastrowid
    except mysql.connector.Error as err:
        print(f"Error executing query: {err}")
        if conn:
            conn.rollback()   # 发生错误时回滚事务。
        raise
    finally:
        if cursor:
            cursor.close()
        if conn:
            close_mysql_connection(conn)

# 批量执行 SQL
def execute_many(query, params_list):
    """
    Execute a SQL query with multiple parameter sets.
    
    Args:
        query (str): The SQL query to execute.
        params_list (list): List of parameter tuples.
        
    Returns:
        int: The number of rows affected.
    """
    conn = None
    cursor = None
    try:
        conn = get_mysql_connection()
        cursor = conn.cursor()
        
        cursor.executemany(query, params_list)
        conn.commit()
        
        return cursor.rowcount
    except mysql.connector.Error as err:
        print(f"Error executing query: {err}")
        if conn:
            conn.rollback()
        raise
    finally:
        if cursor:
            cursor.close()
        if conn:
            close_mysql_connection(conn) 